技巧：
1.where 条件 is null
select ename from emp where mgr is null2.between
包含num1和num2
3.where group by having order by 的顺序
SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
4.更新多个记录
alter table stu add 平均成绩之和 float
select * from stu
update stu set 平均成绩之和=(select 平均成绩+新平均成绩 from stu a where a.学号=stu.学号)5.级联删除
alter table 表名
add constraint FK_ 表名1_ 字段名 foreign key (字段名1) references 表名2(字段名 2) on delete cascade6.规定大写
collate Chinese_PRC_CS_AI
select 商品名称,单价 from 商品信息 where 商品名称 like '%O%' collate Chinese_PRC_CS_AI表的基础操作(增删改查)
表的创建
create table 表名 (字段名1 字段类型,字段名2 字段类型 ,…)
增：insert into 表名(字段名1 字段类型,字段名2 字段类型 ,…) values(值1,值2,…)
删：delete from 表名 (删除全部数据)
改：update 表名 set 字段名=值
查： select * from 表名
条件：like 模糊匹配
基础语句（select,delete,update） where like ‘%…%’
案例：
商品表，teacher表
delete 商品信息 where 产地 like "%州%"
insert into 商品信息(商品编号,商品名称,库存编号,供应商编号, 产地,单价,均价) values(1021,"4G DDR3内存",1008,1002,"广州市",199,null)
update teacher set prof=null where sex="女"
select * from teacher where sex='女';
表和列的增改
为课程表增加一个备注字段，字段名bz,非Unicode编码
alter table 课程 add bz Text将学生表的CJ字段改为成绩,CS改为出生
exec sp_rename 'stu.CJ','成绩','column';
exec sp_rename 'XS.CS','出生','column';使用SQL语句创建借阅表，表名为JY，该表含书号字段SH注意字段名大小写(字符型,最多允许7个汉字或14字符)，学号字段XH(字符型,最多允许5个汉字或11字符)，借阅日期字段JYRQ和归还时间字段GHSJ都是DateTime类型，并添加如下内容(日期格式可能不同,注意字段名大小写)：
SH XH JYRQ GHSJ
1 20120881101 2011-02-21 2013-04-16
create table JY(SH varchar(14),XH varchar(11),JYRQ datetime,GHSJ datetime);
insert into JY(SH,XH,JYRQ,GHSJ) values('1','20120881101','2011-02-21','2013-04-16') ;已知学生表XS有学号、姓名、毕业院校、成绩(CJ)等字段，请写一个SQL语句将删除成绩(CJ)字段
alter table XS drop column CJ已知学生表XS有学号、姓名、毕业院校、成绩(CJ)等字段，请写一个SQL语句成绩(CJ)字段类型改为浮点型(Float)。
alter table xs alter CJ float记录的增删改
1.已知商品信息表含有商品编号,商品名称,库存编号,供应商编号,产地,单价,均价等字段，请写一个SQL语句将各供应商所有商品单价的平均价格写入均价字段。执行结果如下：
供应商编号 商品名称 单价 均价
1001 DRAGONKING 1GB内存 805 497.5
1001 CREATIVE SBS 2.1 380音箱 190 497.5
1002 Intel D915GVWB主板 863.5 1046.65
1002 三星 795MB CRT显示器 1229.8 1046.65
1003 Maxtor 40G硬盘 514.25 514.25
…
update 商品信息 set 均价=(select avg(单价) from 商品信息 a where a.供应商编号=商品信息.供应商编号)
select * from 商品信息 order by 供应商编号 2.现要对学生表(xs)的成绩(字段名为CJ)进行加分，加分的规则是成绩为偶数的加1分，成绩为奇数的加2分
update xs set cj=case
when cj%2=0 then cj+1
when cj%2!=0 then cj+2
/*when CJ=NULL then NULL*/
end3.已知学生表XS有学号、姓名、毕业院校、成绩(CJ)等字段，请写一个SQL语句将不及格学生的学号和姓名按学号升序写入补考名单表(该表已经存在不能再创建)中。
insert into 补考名单(学号,姓名) select 学号,姓名 from xs where cj<60 order by 学号4.已知学生表XS有学号、姓名、毕业院校、成绩(CJ)等字段，请写一个SQL语句将不及格学生的学号和姓名按学号升序写入resit表(该表不存在，执行时创建)中
select 学号,姓名 into resit from xs where cj<60 order by 学号5.已知商品信息表有商品编号、商品名称、产地等字段，请写一个SQL语句将各产地及其商品数信息按产地升序写入产地商品数表(该表已经存在不能再创建)中
insert into 产地商品数 select 产地,count(*) from 商品信息 group by 产地 order by 产地6.已知计算机登录信息表PCInfo含有XH,Host,LogT等字段，该表用于每台计算机登录服务器的时间，现要求只保留每台计算机最后一次登录的信息，请写一个SQL语句将各计算机历史登录信息删除
delete from Pcinfo where xh <(select max(xh) from pcinfo a where a.host=pcinfo.host)7.已知商品信息表有商品编号、商品名称、供应商编号等字段，供应商信息表有编号、供应商名称、产品数量等字段，请写一个SQL语句统计各供应商的产品数量并写入供应商信息表产品数量字段中
update 供应商信息 set 产品数量 =(select count(商品名称) from 商品信息 where 供应商信息.编号=商品信息.商品编号 group by 供应商编号)
update 供应商信息 set 产品数量=0 where 产品数量 is null简单查询
1.显示每个雇员的年工资(基础工资加奖金)
Select sal*13+isnull(comm*13,0) from emp2.显示所有员工中的最低工资和该员工的名字
Select ename from emp where sal=(select min(sal) from emp)3.已知学生表XS有学号、姓名、毕业院校、成绩(CJ)、出生(CS)等字段，请写一个SQL语句显示姓名中含有“荣”的学生姓名
select 姓名 from xs where 姓名 like '%荣%'4.已知学生表XS有学号、姓名、毕业院校等字段，请写一个SQL语句显示毕业院校含“职业”两个字的学生学号，并按升序显示
select 学号 from xs where 毕业院校 like '%职业%' order by 学号5.已知选修表score(sno,cno,degree),查询课程号(cno)为’c02’和’c03’课程的选课人数
select cno,count(*) 选课人数 from score where cno='c02' or cno='c03' group by cno 6.已知学生表XS有学号、姓名、毕业院校、成绩(CJ)等字段，请写一个SQL语句显示所有参加考试学生的平均成绩(保留4位小数)。结果显示如下：
平均成绩
——————
76.0625
select convert( decimal(15,4),avg(1.0*CJ)) AS 平均成绩 from xs
select cast(avg(1.0*CJ) as decimal(15,4) ) AS 平均成绩 from xs7.已知教师表teacher(tno,tname,sex,birthday,prof,depart)，查询姓名(tname)为两个汉字的教师工号(tno)、姓名(tname)、职称(prof)和所在院系(depart)等信息
select tno,tname,prof,depart from teacher where tname like '__'8.已知学生表XS(学号,姓名,CS,毕业院校,CJ)，请写一个SQL语句显示都有哪些毕业院校，一个院校有多个学生，只要显示一次，并按升序显示
select distinct 毕业院校 from XS order by 毕业院校表的复杂查询
1.Group by 和having 语句
Group by对查询结果进行分组统计
Having用于 限制分组产生的结果
例：
显示每个部门的平均工资和最高工资
Select avg(sal),max(sal),deptno from emp group by deptno显示每种部门每种岗位的平均工资和最低工资
Select avg(sal),min(sal),deptno ,job from emp group by deptno,job order by deptno总结：
① 如果select语句中同时包含，group by,having,order by时，那么顺序是：group by having oreder by
② 在选择列中如果有列，分组和表达式，那么这些列和表达式必须有一个出现在group by子句中，否则就会出错。
2.多表查询
查询部门名字为sales的雇员名字
Select * from emp,dept where dname=’sales’and emp.deptno=dept.deptno(注意笛卡尔集)
显示雇员名，雇员工资及所在部门的名字，部门号
如果两张表有相同的字段名，则需要带表名（别名）
Select ename,sal,dname,**emp.deptno** from emp,dept where dept.deptno=emp.deptno或
Select ename,sal,dname,**e.deptno** from emp e,dept d where e.deptno=d.deptno显示部门号为10的部门名，员工名和工资
Select dept.dname,emp.ename,emp.sal from dept,emp where emp.deptno=10 and emp.deptno=dept.deptno3.自连接
指的是在同一张表中的连接查询
例：
显示每个员工和他上级的名字
把emp看作两张表，一个是a,一个是b
Select a.ename 雇员,b.ename 老板from emp a,emp b where a.mgr=b.empno4.子查询
指的是嵌入在其他sql语句中的select语句
1）子查询的分类：
独立子查询
子查询可以独立运行
相关子查询
子查询引用了父查询的结果
select * from stu where stuno=(select stuno from result where stuno=stu.stuno)作为结果集(查询7班的男生信息)，一定要为结果集起个别名
select * from stu where classid=’7’
select * from (select * from stu where classid=’7’
) as tmp where sex=’男’2）单行子查询
只返回一行数据的子查询语句
3）多行查询
返回多行数据的查询
例：显示与SMITH同一部门的所有员工
Select * from emp where deptno=(select deptno from emp where ename=’smith’)查询和部门10工作相同的雇员名字，岗位，工资，部门号
select ename,job,sal,deptno from emp where job in (select distinct job from emp where deptno=10)在from子句中使用子查询
如何显示高于部门平均工资的员工信息
首先求出各个部门的平均工资
Select avg(sal) 平均工资,deptno from emp group by deptno;把查询结果当作一个临时表看待
(select avg(sal) myavg,deptno from emp group by deptno) tmp最终结果
select * from emp ,(select avg(sal) myavg,deptno from emp group by deptno) tmp
where emp.deptno=tmp.deptno and emp.sal>tmp.myavg查询最近一次office考试的最高分和最低分
select MAX(stuResult) 最高分,min(stuResult) as 最低分from stu where subjectid=(
select subjectid from subject where subject=’office’) and
examdate = (
select max(examdate) from result where subjectid=(select subjectid from subject where subject=’office’
)
)5. 分页查询
1)不用分页函数
字段 ename deptno sal hiredate
显示第一个到第四个的入职的雇员
select top 4 * from emp order by hiredate;--top后的数表示要选择几条记录显示第四个到第9个入职的职员信息
select top 6 * from emp where ename not in
(select top 3 ename from emp order by hiredate) order by hiredate;用查询结果创建新表（不存在）
Select *（可选字段名） into 新表名 from 表名
如何删除一张表的重复记录
tid tname tpasswd
1 aaa 123
1 aaa 123
1 aaa 123
2 bbb 456
2 bbb 456
select distinct into emp from test;--创建一个临时表
delete from test ; --删除原表的所有数据
insert into test select from temp ; --将临时表的数据插入原表
drop table temp ; --删除临时表2）使用分页函数
select * from (select ROW_NUMBER() over(order by tid) id,* from stu) tmp where id >=5 and id<=8注意：如果使用生成行号的子查询作为结果集，那么必须为结果集添加别名，并且为行号添加名称
ROW_NUMBER() over(排序字段)：根据指定的字段排序，对排序之后的每一行添加一个不间断的行号
6.维护数据的完整性
Not null
Unique 当定义了唯一约束后，该值不能重复，可以空但是最多只能有一个
Primary key
一张表只能有一个主键，但是可以有多个unique约束
复合主键
create table test3(
tid int,
tname varchar(20),
tpasswd nvarchar(20)
primary key (tid,tname)
)Foreign key(外键)
定义主表和从表之间的关系，外键的约束要定义在从表上，主表必须有主键约束或者unique约束
Check 强制数据必须满足的条件
create table test4(
tid int,
tname varchar(20),
tpasswd nvarchar(20),
tage int check(tage>=0 and tage<=120)
)商品售货系统表设计案例
create table goods(
goodsid nvarchar(20) primary key,
goodsname varchar(20) not null,
unitprice numeric(10,2) check(unitprice>0),
category nvarchar(3) check(category in('食物','日用')),
provider nvarchar(50)
)
create table customer(
customerid nvarchar(20) primary key,
custname nvarchar(20),
custaddress nvarchar(100),
email nvarchar(100) unique,
sex nchar(1) check (sex in ('男','女')) default '男',
cardid nvarchar(18)
)
create table purchase(
customerid nvarchar(20) foreign key references customer(customerid),
goodsid nvarchar(20) foreign key references goods(goodsid),
nums int check (nums>0)
)表的连接
1.已知字母表L的内容如下：
C
————————
A
B
C
D
E
请写一多表查询语句，显示5个字母中任取3字母的组合，内容如下(按组合从小到大排序)：
组合
————————
ABC
ABD
ABE
ACD
ACE
ADE
BCD
BCE
BDE
CDE
select L1.C+L2.C+L3.c 组合 from L L1,L L2,L L3 where L1.c<L.c and L2.c<L3.c2.请根据学生表XS(学号,姓名,CS,毕业院校,CJ)、选修表XX(xh,kh,CJ)、课程表C(kh,km,RS)，写一多表查询语句，显示同一天生日的学生信息，内容如下：
学号 姓名 学号 姓名 BIRTH
————————————————————
20100881205 王聪慧 20100881210 郭闻娟 01-01
20100881205 王聪慧 20100881211 邹艺荣 01-01
select a.学号 ,a.姓名 ,b.学号 ,b.姓名, right(convert(varchar(10),a.CS ,120),5) BIRTH from xs a , xs b where a.学号<b.学号 and right(convert(varchar(10),a.CS ,120),5)=right(convert(varchar(10),b.CS ,120),5)3.请根据学生表XS(学号,姓名,CS,毕业院校,CJ)、选修表XX(xh,kh,CJ)、课程表C(kh,km,RS)，写一多表查询语句，显示各课程的选修情况(含无人选修课程信息)。
XH KH KM CJ
————————————————————
20100881201 1001 C语言 80
20100881203 1001 C语言 56
select xs.XH,XX.KH,c.KM,xx.CJ from xs left join xx on xs.学号=xx.xh right join c on c.kh=xx.kh case 语句
1)如果case后面跟表达式或字段，那么这种结构只能做等值判断，不能判断null值
case 表达式/字段
when 值then 自定义值
else 自定义值
end
2) 如果case后面没有接表达式或值，那么这种结构相当于if…else
,它可以判断null值
case
when 值then 自定义值
else 自定义值
end
数据完整性
简介
实体完整性：表的每一行数据就称为一个实体，实体完整性是指 每一行记录是唯一的，不重复的
标识列：系统自动生成，永远不会重复
主键：唯一 非空
唯一键：唯一 但是可以为null，只能空一次
域完整性：域就是字段，域完整性就是为了保证字段的值是合理和准确的
非空 类型，check约束，默认值，关系（主外键约束）
自定义完整性：用户自己定义的约束规则
check约束 存储过程 触发器
引用完整性：一个表的字段的值引用另外一个表的某一个字段，
被引用的表称为主表，引用表就是称为从表或者外键表
1.选择外键表去创建主外键关系
2.建立主外键关系的字段类型和意义必须一致
3.建立关系的字段 在主表中必须是主键或者是唯一键
4.添加数据的时候先添加主表数据，再添加外键表
5.删除数据的时候先删除外键表数据，再删除主表
关系建立后表的级联操作
1.不执行任何操作：该报错就报错，能删除就删除
2.级联：删除主表记录，对应的从表记录也将被删除
3.set null ：删除主表，从表对应记录的字段值=null，前提是这个字段可以设置为null
4.set default :删除主表，从表对应记录的字段值=设置的默认值，前提是这个字段已经设置了默认值
使用代码创建约束
–种类：主键约束（primary key PK） 唯一键约束(unique UQ) 检查约束（check CK ） 默认值约束(default DF)
–外键约束(Foreign key FK)
–创建约束的语法：
–alter table 表名
–add constraint 约束的名字(以简写作为前缀) 约束的类型(字段,表达式 值)
use test;
create table Teacher (
id int not null,
Name nvarchar(50) not null,
Gender bit not null,
Age int ,
Salary money,
Birthday datetime not null
)teacher表
classes表
create table classes(cid int identity(1,1) constraint PK_classes_cid primary key ,cname varchar(20))
–1.将id设置为主键
alter table Teacher
add constraint PK_Teacher_Id primary key(id)–2.设置name为唯一键
if exists(select * from sysobjects where name='UQ_Teacher_Name')
alter table teacher drop constraint UQ_Teacher_Name
alter table Teacher
add constraint UQ_Teacher_Name unique(Name)–3.设置年龄0-100之间
if exists(select * from sysobjects where name='CK_Teacher_Age')
alter table teacher drop constraint CK_Teacher_Age
alter table teacher
ADD constraint CK_Teacher_Age check(Age>0 and Age<100)–4.为birthday添加默认值
if exists(select * from sysobjects where name='DF_Teacher_Birthday')
alter table teacher drop constraint DF_Teacher_Birthday
alter table teacher
add constraint DF_Teacher_Birthday default('1999-9-9') for birthday --for是说明为哪一个字段添加–5.为ClassId添加外键约束
alter table teacher --从表的某一个字段，引用主表的某一个字段
add constraint FK_teacher_ClassId foreign key(classid) references classes(cid)级联操作
if exists(select * from sysobjects where name='FK_teacher_ClassId')
alter table teacher drop constraint FK_teacher_ClassId
alter table teacher --从表的某一个字段，引用主表的某一个字段
with nocheck --不检查现有数据
add constraint FK_teacher_ClassId foreign key(classid) references classes(cid)
--on delete Set null --删除主表数据，从表引用的数据设为nullsubject表
student表
result表
1.设置外键
create table dept(depno int primary key)
create table emp(empno int primary key,ename nvarchar(3),deptno int foreign key references dept(deptno))外键的数据类型和主键一致
外键只能指向主键
保持数据的完整性
视图
简介
视图就是一张虚拟表，根据用户的查询创建的命令，使用视图就像使用表一样
select * from vw_getinfo
create view view_emp as
select ename,hiredate,sal
from emp;
select * from view_emp;
drop view view_emp;使用代码创建视图
–create view vw_视图名称
–as
–查询的命令
–go
use ls;
if exists(select * from sysobjects where name='vw_stuinfo')
drop view vw_stuinfo
go
create view vw_stuinfo --这个创建语句必须是批处理的第一句
as
--select * from stu where 性别='男'
--update stu 不能在视图中使用update,delete,insert
--视图中只能使用一条select命令
--视图不能使用order by，除非同时使用了top
select top 99.9999 percent * from stu order by 姓名
go注意
–对视图一般不去执行增加删除和修改的操作，需要注意的是，
–这些操作会直接影响原始的物理表,d但是这些操作局限于单表
–使用视图
select * from vw_stuinfo
update vw_stuinfo set java=java-100 where 性别='男'例：
对学生表Stu(XH,XM,CJ)创建加密视图学生加密(学号,姓名,成绩)。运行后结果显示：
学号 姓名 成绩
——————————————————————
1 AAA 56
2 BBB 80
3 CCC 70
Text
——————————————————————
NULL
go
create view 学生加密
with encryption
as
select XH 学号,XM 姓名,CJ 成绩 from stu
go对学生表Stu(XH,XM,CJ)以带检查选项创建视图学生及格(学号,姓名,成绩)，该视图只包含成绩及格学生的信息，然后查询该视图内容，最后将成绩70改为50。运行后结果显示：
学号 姓名 成绩
——————————————————————
2 BBB 80
3 CCC 70
E_No Lever State 错误信息
————————————————————————
550 16 1 试图进行的插入或更新已失败，原因是目标视图或者目标视图所跨越的某一视图指定了 WITH CHECK OPTION
go
create view 学生及格
as
select XH 学号,XM 姓名,CJ 成绩 from stu where CJ>=60
WITH CHECK option
go函数
函数和存储过程类似。
1.编写一函数Fact(@N)，用于求@N!，当@N=5时显示结果如下：
——————————————
120
go
create function Fact(@num int)
returns int
as
begin
declare @sum int =1
declare @i int =1
while(@i<=@num)
begin
set @sum=@sum*@i
set @i=@i+1
end
return @sum
end
go2.已知学生表XS有学号、姓名、毕业院校、成绩(CJ)等字段。创建一个函数XH2XM(@XH)，其功能是返回指定学号(@XH)学生的姓名。运行结果如下：
姓名
——————————
王聪慧
姓名
——————————
游连桦
go
create function XH2XM(@XH varchar(11))
returns varchar(11)
as
begin
declare @name varchar(11)
set @name=(select 姓名 from xs where 学号=@XH )
return @name
end
go局部变量
简介
–语法
–declare @变量的名字 变量的类型=[默认值]
declare @name nvarchar(20)='zzy'
print @name+'1346'
select @name
go赋值 set select
–如果后面是完整的子查询，那么两个没有任何的区别
declare @name nvarchar(20)
set @name='3radsaf'
select @name='456789'–查询比学号44的年龄大的学生信息
go
declare @obj int
select @obj=(select 年龄 from stu where right(cast( cast (学号 as decimal(11,0) )as varchar(11) ),2)='44')
--set @obj=(select 年龄 from stu where right(cast( cast (学号 as decimal(11,0) )as varchar(11) ),2)='44')
select * from stu where 年龄>@obj两种赋值方式的区别
–1.
–set一次只能为一个变量赋值,select支持一次为多个变量
go
declare @name nvarchar(20),@age int
select @name='111',@age=20
--set @name='111',@age=20–2.
–如果=后是不完整的sql语句，那么当语句返回多行一列的时候,select会得到最后一个值
go
declare @name nvarchar(20),@age int=20
--当没有用 EXISTS 引入子查询时，在选择列表中只能指定一个表达式。
--子查询返回的值不止一个。当子查询跟随在 =、!=、<、<=、>、>= 之后，或子查询用作表达式时，这种情况是不允许的。
--set @name =(select 姓名 from stu)
--set @name = 姓名 from stu set后只能接独立子查询
select @name = 姓名 from stu
print @name --最后一个名字–如果=后边的查询语句没有返回值，那么select会保留默认值
go
declare @name nvarchar(20)='safaf',@age int=20
--set @name=(select 姓名 from stu where right(cast( cast (学号 as decimal(11,0) )as varchar(11) ),2)='88')
--等价于select @name=(select 姓名 from stu where right(cast( cast (学号 as decimal(11,0) )as varchar(11) ),2)='88')
select @name=姓名 from stu where right(cast( cast (学号 as decimal(11,0) )as varchar(11) ),2)='88'
print @name
print '13214'变量使用案例
–查询最近一次office考试的最高分和最低分
go
declare @subName varchar(20)='office'--科目名称
declare @subId int --科目id
set @subId=(select subId from subject where subName=@subName)--获取科目id
declare @time datetime--最近一次考试时间
select @time=max(examDate) from Result where subId=@subId --获得这一科目最近考试时间
select MAX(stuResult),MIN(stuResult) from Result where subId=@subId and examDate=@time全局变量（系统变量）
全局变量必须以标记@@作为前缀，如
@@version
全局变量由系统定义和维护，我们只能读取，不能修改全局变量的值
IF ELSE
–没有bool值，只有条件表达式
–没有{}，只有begin..end
–可以多重，可以嵌套
–如果包含的语句只有一句，可以不使用begin…end
–if或else必须有处理语句，否则会报错
if (1=1)
begin
print '2313'
print '5846'
end–计算java成绩的平均成绩，如果平均分超过60分输出前三名的成绩。否则输出后三名的成绩
go
declare @avg float
set @avg=(select avg(java) from stu)
print @avg
if (@avg>=60)
begin
select top 3 * from stu order by java desc
end
else
begin
select top 3 * from stu order by java
end
while
–没有bool值，只有条件表达式
–没有{}，只有begin..end
–可以多重，可以嵌套
–可以使用continue/break语句
如果java考试布局个的人数超过了半数，题目出难了，就让每个同学的分数加2分，直到不及格的人数少于一半
go
declare @nopassnum int
declare @totalnum int
select @totalnum=count(*) from stu
set @nopassnum=(select count(*) from stu where java <60 and java<98)
while(@nopassnum>=@totalnum/2)
begin
update stu set java+=2
set @nopassnum=(select count(*) from stu where java <60 and java<98)
end事务
事务：这些语句要么都成功执行，要么都不执行。事务只是一种处理机制
–事务是对有可能对表的数据进行更改的操作而言（增加，删除，修改），对查询没用
–事务的特点：CUID
–1.原子性：事务不可以再分，事务时原子工作单元，对于其数据的修改，要么全部执行，要不全部不执行
–2.一致性：事务处理后，数据要保持某种程度的一致性
–3.隔离性：每一个事务都是独立的，不受其他事务的影响
–4.持久性：事务一旦提交，对数据的修改永久保留
name salary
aa 1000
bb 1000
–使用事务进行转账
alter table bank add constraint bank_salary check(salary >0)--添加约束，salary必须>0
declare @err int =0
begin transaction
update bank set salary-=1000 where name='aa'
set @err+=@@ERROR --累加此行的错误号
--if(@err<>0)--有错误,
-- rollback transaction--不能某一句出现错误，就进行回滚或者提交
update bank set salary+=1000 where name='bb'
set @err+=@@ERROR
if(@err<>0)--有错误,只有全部语句执行完之后，在进行判断
rollback transaction
else
commit transaction
select * from bankCOMMIT：提交更改；
ROLLBACK：回滚更改；
SAVEPOINT：在事务内部创建一系列可以 ROLLBACK 的还原点；
SAVEPOINT 是事务中的一个状态点，使得我们可以将事务回滚至特定的点，而不是将整个事务都撤销。
savepoint
SAVEPOINT SAVEPOINT_NAME;
ROLLBACK TO SAVEPOINT_NAME;
存储过程
简介
存储过程–就像数据库中运行方法（函数）
–方法说明：
–方法名称：参数 调用 返回值
–参数：主要是一一对应的原则
– 1.类型对应：子类可以替换父类 int–double
– 2.数量对应：默认值，可变参数
– 3.顺序对应：可以使用 参数：值的方法调用
–返回值：通过return 返回值 ，但是只能返回单个值
–可以通过ref/out扩展方法的’返回值’
–创建存储过程的语法
–go
–create procedure usp_存储过程名称
–(形参) 可以在这个里面定义参数
–as–相当于方法体
–{
– 自定义局部变量
– 逻辑语句
–}
–go
无参数
–.查询所有学生信息
if exists(select * from sysobjects where name='usp_getAllInfo')
drop procedure usp_getAllInfo
go
create procedure usp_getAllInfo --简写proc
as
select * from emp
go
--调用存储过程，获取学生信息
execute usp_getAllInfo --简写exec单参数
查询指定性别的学生信息
if exists (select * from sysobjects where name='usp_getAllInfoBySex')
drop proc usp_getAllInfoBySex
go
create proc usp_getAllInfoBySex
@sex char(2) --形参只是声明，不是定义，所以不需要declare
as
select * from emp where sex=@sex
go
exec usp_getAllInfoBySex '男'--相当于'男孩'多参数
获取指定性别和班级名称的学生信息
if exists(select * from sysobjects where name ='usp_getAllInfoBySexAndClassName')
drop proc usp_getAllInfoBySexAndClassName
go
create proc usp_getAllInfoBySexAndClassName
@sex char(2),
@className varchar(50)
as
declare @classId int --科目ID
set @classId=(select * from grade where className=@className)
select * from Student where sex=@sex and classId=@classId
go
exec usp_getAllInfoBySexAndClassName '男','三班'默认参数
–创建有默认值的存储过程
if exists(select * from sysobjects where name ='usp_getAllInfoBySexAndClassName')
drop proc usp_getAllInfoBySexAndClassName
go
create proc usp_getAllInfoBySexAndClassName
@className varchar(50),
@sex char(2)='女' --往往会将有默认值的参数写在所有参数列表的最后，这样实参列表就可以省略default
as
declare @classId int --科目ID
set @classId=(select * from grade where className=@className)
select * from Student where sex=@sex and classId=@classId
go
--参数传递顺序一致：第一个实参默认就是传递给第一个形参
--如果有默认值，那么可以使用default
exec usp_getAllInfoBySexAndClassName default,'三班'
--也可以使用 参数=值的方式调用存储过程，这样就与顺序没有关系了，
--一旦使用了@name=value 之后，那么后续的参数就必须以@name=value的形式传递
--exec usp_getAllInfoBySexAndClassName @className='三班',@sex='男'创建带有输出参数的存储过程
--根据性别和班级查询学员，同时返回总人数和指定性别的人数
if exists(select * from sysobjects where name='usp_getInfoAndCount')
drop proc usp_getInfoAndCount
go
create proc usp_getInfoAndCount
@totalnum int output,--如果一个参数添加了output修饰，那么说明:它是一个输出参数,
--说明了你会向服务器请求返回这个参数的值,服务器也知道标识了output的参数在以后需要返回
@clanum int output ,--指定班级和性别的人数
@className nvarchar(50),--输入参数:需要用户传入值
@sex char(2)
as
declare @classId int =(select classId from grade where className=@className)--根据班级名称获取班级ID
select * from Student where classId=@classId and sex=@sex
select count(*) from Student --总人数
select count(*) from Student where sex=@sex and classId=@classId
go调用有输出参数的存储过程
–服务器返回值，需要创建对应的变量来接收
declare @tnum int ,@cnum int
execute usp_getInfoAndCount @tnum,@cnum,'三班'
print @tnum
print @cnum使用output的情况：
1.返回多个值 2.返回的数据类型为非整型
（return 只能返回整型）–返回指定人数
if exists(select * from sysobjects where name='usp_getNameByNo')
drop proc usp_getNameByNo
go
create proc usp_getNameByNo
@cid int
as
declare @cnt int
set @cnt=(select count(*) from Student where classId= @cid)
--return 只能返回整数值
return @cnt
go
--执行存储过程，接收return的返回值
declare @count int
execute @count= usp_getNameByNo 6
print @count触发器
–语法:
–create trigger tr_触发器名称
–on 表 after(for)/instead of 增加删除(delete) 修改
–as
–任意的逻辑代码 –存储过程
–go
Create Trigger 触发器名 On {表|视图}
[With Encryption]
{{For|After|Instead of} {[Insert][,][Delete][,][Update]}[Not For Replication]
as
SQL语句[,…n]For：如果仅指定For关键字，而没指定After或Instead of，则默认After。
实例：
if exists(select * from sysobjects where name ='tr_emp_insert')
drop trigger tr_emp_insert
go
create trigger tr_emp_insert
on emp for insert --为emp表创建触发器，在你对grade表进行插入操作后触发
as
select * from emp
go
insert into emp(ename) values(10)
select * from inserted --操作之后，新表(操作过后的表)
select * from deleted --操作之前，旧表（操作前的表）–两个临时表
if exists(select * from sysobjects where name ='tr_emp_insert')
drop trigger tr_emp_insert
go
create trigger tr_emp_insert
on emp for insert
as
print 'inserted表存储操作之后的 与当前操作相关的数据 ，而与之前表的数据无关'
select * from inserted
print 'deleted表存储操作之前的数据'
select * from deleted
go
insert into emp(ename,sex) values (12,'男')记录增删改操作触发器中使用了两个逻辑(概念)表：Inserted表和Deleted表，它们的结构与定义触发器的表相同。Deleted保存要被删除的记录值或修改前的记录值(旧值)，Inserted保存要插入的记录值或修改后的记录值(新值)。也就是，在Insert触发器中，Inserted表保存要插入的记录，而Deleted表无记录；在Update触发器中，Inserted表保存修改后的记录，Deleted表保存修改前的记录；在Delete触发器中，Deleted表保存删除前的记录，而Inserted表无记录。
例：为emp表创建触发器，当有记录插入，删除或更新时，输出多少条被记录被插入，多少条被记录被删除，多少条被记录被更新
go
create trigger StuIDU on emp
for insert,delete ,update
as
declare @insertnum int ,@deletenum int
set @insertnum=(select count(*) from inserted)
set @deletenum=(select count(*) from deleted)
if @insertnum >0 and @deletenum>0
print cast(@insertnum as varchar(5))+'条记录被更新'
else
if @insertnum >0
print cast(@insertnum as varchar(5))+'条记录被插入'
else
print cast(@deletenum as varchar(5))+'条记录被删除'
go1,已知学生表Stu含XH、XM等字段，分别表示学号和姓名等。现要求创建一个触发器StuIDU，记录增删改操作时触发，触发后显示当前执行的是什么操作和所影响的记录数。
If Exists(Select * From Sys.Objects Where Type='U' and Name='Stu') --判断是否有Stu表
Drop Table Stu
Create Table Stu(XH int primary key,XM varchar(8));
GO
Create Trigger StuIDU On Stu After Insert,Delete,Update As
Begin
Declare @In int,@Dn int
Select @In=Count(*) From Inserted
Select @Dn=Count(*) From Deleted
If @In>0 and @Dn>0 --
Print str(@In)+'条记录被更新'
Else
If @In>0
Print str(@In)+'条记录被插入'
Else
Print str(@Dn)+'条记录被删除'
End
GO
Insert Into Stu(XH,XM) Values(1,'AAA');
Insert Into Stu(XH,XM) Select 2,'BBB' Union Select 3,'CCC'
Update Stu Set XH=XH+1 where XH>1
Delete Stu
--运行结果显示：
--1条记录被插入
--2条记录被插入
--2条记录被更新
--3条记录被删除2,已知学生图书借阅管理系统有三张表,图书表TuShu含书号、书名、册数等字段，读者表DuZhe含XH、XM等字段，分别表示学号和姓名，借阅表JieYue含SH、XH、JYRQ、GHRQ等字段，分别表示书号、学号、借阅日期、归还日期。现要求创建相关触发器，实现每借阅一本图书(插入一条借阅记录)，图书的库存册数自动减1，每归还一本图书(删除一条借阅记录)，图书的库存册数自动加1，更换借阅图书，库存册数作相应调整。
If Exists(Select * From Sys.Objects Where Type='U' and Name='TuShu') --判断是否有TuShu表
Drop Table JieYue,TuShu,DuZhe
Create Table TuShu(书号 varchar(14),书名 varchar(20),册数 int);
Insert Into TuShu Values('9787302161801','Windows程序设计',2);
Insert Into TuShu Values('9787302161802','C++程序设计',3);
Insert Into TuShu Values('9787302161803','Win32汇编语言',4);
Create Table DuZhe(XH Varchar(14),XM Varchar(8));
Insert Into DuZhe(XH,XM) Values('20120881101','黄惠珍');
Insert Into DuZhe(XH,XM) Values('20120881102','肖光');
Create Table JieYue(SH varchar(14),XH varchar(11),JYRQ DateTime,GHRQ DateTime);
If Exists(Select * From Sys.Objects Where Type='TR' and Name='Jie') --判断是否有Jie触发器
Drop Trigger Jie,Huan,GengHuan
GO
--创建借阅触发器
Create Trigger Jie On JieYue After Insert As
Update TuShu set 册数=册数-1 Where 书号 IN (Select SH From Inserted) --被借图书册数减1
GO
--创建归还触发器
Create Trigger Huan On JieYue After Delete As
Update TuShu Set 册数=册数+1 Where 书号 IN (Select SH From Deleted) --归还图书册数加1
GO
--创建更换触发器
Create Trigger GengHuan On JieYue After Update As
Update TuShu Set 册数=册数-1 Where 书号 IN (Select SH From Inserted)
Update TuShu Set 册数=册数+1 Where 书号 IN (Select SH From Deleted)
GO
Insert Into JieYue Values('9787302161802','20120881101',GetDate(),GetDate()+20);
Insert Into JieYue Values('9787302161803','20120881101',GetDate(),GetDate()+20);
Select * From JieYue
Select * From TuShu
GO
Update JieYue Set SH='9787302161801' Where SH='9787302161803'
Select * From JieYue
Select * From TuShu
GO
Delete JieYue
Select * From JieYuee
Select * From TuShu
/*
运行结果显示：
SH XH JYRQ GHRQ
-------------- ----------- ----------------------- -----------------------
9787302161802 20120881101 2016-11-21 18:43:58.903 2016-12-11 18:43:58.903
9787302161803 20120881101 2016-11-21 18:43:59.107 2016-12-11 18:43:59.107
书号 书名 册数
-------------- --------------- -----------
9787302161801 Windows程序设计 2
9787302161802 C++程序设计 2
9787302161803 Win32汇编语言 3
SH XH JYRQ GHRQ
-------------- ----------- ----------------------- -----------------------
9787302161802 20120881101 2016-11-21 18:43:58.903 2016-12-11 18:43:58.903
9787302161801 20120881101 2016-11-21 18:43:59.107 2016-12-11 18:43:59.107
书号 书名 册数
-------------- --------------- -----------
9787302161801 Windows程序设计 1
9787302161802 C++程序设计 2
9787302161803 Win32汇编语言 4
SH XH JYRQ GHRQ
-------------- ----------- ----------------------- -----------------------
书号 书名 册数
-------------- --------------- -----------
9787302161801 Windows程序设计 2
9787302161802 C++程序设计 3
9787302161803 Win32汇编语言 4
*/3.创建简易酒店管理系统相关触发器，要求每入住一个旅客(插入一个旅客记录)，客房入住人数自动加1，每退房一个旅客(删除一个旅客记录)，客房入住人数自动减1，旅客调整房间，客房入住人数作相应调整。
go
create trigger tr_house on 旅客
for delete,insert,update
as
declare @innum int
declare @denum int
select @innum=(select count(*) from inserted)
select @denum=(select count(*) from deleted)
if @innum >0 and @denum<=0
update 客房 set 入住人数=入住人数+1 where 房号 in (select 房号 from inserted)
else if @innum >0 and @denum > 0
begin
update 客房 set 入住人数=入住人数+1 where 房号 in (select 房号 from inserted)
update 客房 set 入住人数=入住人数-1 where 房号 in (select 房号 from deleted)
end
else if @innum <=0 and @denum>0
begin
update 客房 set 入住人数=入住人数-1 where 房号 in (select 房号 from deleted)
end
go
Select * from 客房
Insert Into 旅客(身份证号,姓名,房号) Values('123…','AAA',101)
Select * from 客房
Update 旅客 Set 房号=201 Where 姓名='AAA'
Select * from 客房
Delete 旅客 Where 姓名='AAA'
Select * from 客房
/*
运行后结果显示：
房号 入住人数
---------- ----------
101 0
201 0
房号 入住人数
---------- ----------
101 1
201 0
房号 入住人数
---------- ----------
101 0
201 1
房号 入住人数
---------- ----------
101 0
201 0
*/练习
1.已知学生表XS有学号、姓名、毕业院校、成绩(CJ)等字段，请写一个SQL语句显示毕业院校的学生人数多于1个的毕业院校及其人数，并按学生人数升序、毕业院校降序显示。结果显示如下：
毕业院校 人数
——————————————————————
厦门理工学院 4
福建工程学院 7
select 毕业院校 ,count(*) 人数 from xs group by 毕业院校 having count(*) >1 order by 人数2.已知学生表XS有学号、姓名、毕业院校、成绩(CJ)、出生(CS)等字段，请写一个SQL语句显示各分数段及其人数(注意：分数范围为099及NULL)。结果显示如下：49 1
分数段 人数
————————————————
40
5059 269 2
60
7079 489 4
80
90~99 3
~ 4
select 分数段,count(*) 人数 from (
select cj,分数段=
case
when CJ>=0 and CJ<=9 then '0~9'
when CJ>=10 and CJ<=19 then '10~19'
when CJ>=20 and CJ<=29 then '20~29'
when CJ>=30 and CJ<=39 then '30~39'
when CJ>=40 and CJ<=49 then '40~49'
when CJ>=50 and CJ<=59 then '50~59'
when CJ>=60 and CJ<=69 then '60~69'
when CJ>=70 and CJ<=79 then '70~79'
when CJ>=80 and CJ<=89 then '80~89'
when CJ>=90 and CJ<=99 then '90~99'
else '~'
end
from xs) tmp group by tmp.分数段 order by case when 分数段='~' then 1 else 0 end1)–查询图书的信息，要求图书的价格显示为：
–价格为null :显示为价格未知
–价格为10-20：显示为价格10-20
–价格为20-50：显示为价格适中
–其他：原样显示
select bookid,bookname,case
when price is null then '价格未知'
when price <20 and price >=10 then '价格在10-20之间'
when price <30 and price >=20 then '价格适中'
else convert(varchar(10),price)
end as 价格 from book2)
子查询：一个查询中还包含着另外n个查询
分类：独立子查询（直接运行子查询语句）相关子查询（子查询中引用了父查询的结果）
使用方法:1.子查询作为条件：如果跟在> < >= <= …后面要保证子查询只返回了一个值
A.如果子查询返回了一行多列值，可以使用exists/not exists，否则报错
B.如果返回了多行一列值，可以使用in /not in
2.子查询可以作为结果集
A.如果需要用到子查询中的字段，必须保证字段有名字（需要添加新的列名）
B.子查询做结果集必须命名
3.子查询作为列的值
–查询学号是19的学生参加最近一次office课程考试的成绩,要求输出学生的姓名，成绩
–1.子查询
–2.如果没有相应的数据，则可以更改数据表中的数据
select Student.studentName,Result.studentResult from Student inner join Result
on Student.studentNo=Result.studentNo where Student.studentNo=19
and ExamDate=(
select max(ExamDate) from Result where subjectId=(
select subjectId from Subject where SubjectName='office' ) and studentNo=19
)
and subjectId=(
select subjectId from Subject where SubjectName='office'
)–使用变量
go
declare @subjectName varchar(20)='office'--科目名称
declare @subjectId int
set @subjectId =(select subjectId from subject where subjectName=@subjectName)--科目名称
declare @time datetime--考试日期
select @time=(select MAX(examDate) from Result where subjectId=@subjectId and studentNo=19)
select Student.studentName,Result.studentResult from Student inner join Result
on Student.studentNo=Result.studentNo where Student.studentNo=19
and ExamDate=@time and subjectId=@subjectId3）检查学生office最近一次考试的成绩是否有不及格，如有，没人加2分，高于95的学生不加分，直至所有学生及格
go
declare @subjectName varchar(20)='office'--科目名称
declare @subjectId int
set @subjectId =(select subjectId from subject where subjectName=@subjectName)--科目名称
declare @time datetime--考试日期
select @time=(select MAX(examDate) from Result where subjectId=@subjectId)
while(1=1)
begin
if exists(select * from Result where subjectId=@subjectId and examDate=@time and studentResult<60)
begin
update Result set studentResult+=2 where studentResult<=95 and subjectId=@subjectId and examDate=@time
end
else
break;
end4）打印图案
–★
–★★
–★★★
–★★★★
–★★★★★
go
declare @rownum int =1
declare @colnum int =1
declare @graph nvarchar(5)='★'
--set @graph+='★★★'
--print @graph
while(@rownum<=5)
begin
--set @colnum=1
--set @graph=''
--while(@colnum<=@rownum)
-- begin
--set @colnum+=1
print @graph
set @graph+='★'
--end
set @rownum+=1
end
